package com.moinut.studentattendancemanager.model.dao.impl;

import com.moinut.studentattendancemanager.model.dao.ITeacherInfoDAO;
import com.moinut.studentattendancemanager.model.vo.TeacherInfo;

import java.sql.Connection;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

/**
 * @author MOILING
 */
public class TeacherInfoDAOImpl extends BaseDAOImpl implements ITeacherInfoDAO {

    public TeacherInfoDAOImpl(Connection connection) {
        super(connection);
    }

    @Override
    public List<TeacherInfo> getTeachersInfoByStudentId(String studentId) throws Exception {
        List<TeacherInfo> teachersInfo = new ArrayList<>();
        String sql = "SELECT DISTINCT teacher_name, course_name, college_name " +
                     "FROM classstudentinfo, classinfo, teacherinfo " +
                     "WHERE classstudentinfo.student_id = ? " +
                     "AND classstudentinfo.class_id = classinfo.class_id " +
                     "AND classinfo.teacher_id = teacherinfo.teacher_id";
        preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1, studentId);
        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next()) {
            TeacherInfo teacherInfo = new TeacherInfo(resultSet.getString(1),
                    resultSet.getString(2), resultSet.getString(3));
            teachersInfo.add(teacherInfo);
        }
        if (this.preparedStatement != null) {
            this.preparedStatement.close();
        }
        return teachersInfo;
    }

    @Override
    public List<TeacherInfo> getAllTeachersInfo() throws Exception {
        List<TeacherInfo> teachersInfo = new ArrayList<>();
        String sql = "SELECT DISTINCT teacher_name, course_name, college_name " +
                     "FROM  classstudentinfo, classinfo, teacherinfo " +
                     "WHERE classstudentinfo.class_id = classinfo.class_id " +
                     "AND classinfo.teacher_id = teacherinfo.teacher_id";
        preparedStatement = connection.prepareStatement(sql);
        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next()) {
            TeacherInfo teacherInfo = new TeacherInfo(resultSet.getString(1),
                    resultSet.getString(2), resultSet.getString(3));
            teachersInfo.add(teacherInfo);
        }
        if (this.preparedStatement != null) {
            this.preparedStatement.close();
        }
        return teachersInfo;
    }

}
